package com.quanquan.parking.dao.statisticsReport;

import com.quanquan.parking.entity.PayNotify;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.List;

/**
 * @ClassName:CouponReportRepository
 * @Description:专门用来统计停车的接口
 * @Author:xiongjunbao
 * @Date:2019/5/30 13:50
 * @Version 1.0
 */
public interface ParkingReportRepository extends JpaRepository<PayNotify, String> {

    /**
     * 根据天获取停车数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y-%m-%d') AS d,COUNT(1) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findCountByDay();

    /**
     * 根据周获取停车数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y-%u') AS d,COUNT(1) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findCountByWeek();

    /**
     * 根据月获取停车数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y-%m') AS d,COUNT(1) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findCountByMonth();

    /**
     * 根据年获取停车数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y') AS d,COUNT(1) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,1",nativeQuery = true)
    List<Object[]> findCountByYear();


    /**
     * 根据天获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y-%m-%d') AS d,SUM(fee)+SUM(coupon) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,7",nativeQuery = true)
    List<BigDecimal[]> findFeeByDay();

    /**
     * 根据周获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y-%u') AS d,SUM(fee)+SUM(coupon) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,7",nativeQuery = true)
    List<BigDecimal[]> findFeeByWeek();

    /**
     * 根据月获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y-%m') AS d,SUM(fee)+SUM(coupon) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,7",nativeQuery = true)
    List<BigDecimal[]> findFeeByMonth();

    /**
     * 根据年获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(TIME,'%Y') AS d,SUM(fee)+SUM(coupon) AS COUNT FROM oo_pay_notify GROUP BY d ORDER BY d DESC LIMIT 0,1",nativeQuery = true)
    List<BigDecimal[]> findFeeByYear();


}
